package com.citycloud.ccuap.tc.admin.sec.secuser;

import java.util.List;
import java.util.Map;
import java.util.Set;

import org.apache.commons.lang3.StringUtils;
import org.apache.ibatis.annotations.DeleteProvider;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.ResultMap;
import org.apache.ibatis.annotations.ResultType;
import org.apache.ibatis.annotations.SelectProvider;
import org.springframework.cache.annotation.CachePut;
import org.springframework.cache.annotation.Cacheable;
import org.springframework.context.annotation.Lazy;
import org.springframework.data.domain.Sort;

import com.citycloud.ccuap.commons.db.SqlRestrictions;
import com.citycloud.ccuap.commons.sys.entity.SysGroup;
import com.citycloud.ccuap.commons.sys.entity.SysProject;
import com.citycloud.ccuap.commons.sys.entity.SysRole;
import com.citycloud.ccuap.commons.sys.entity.SysUser;
import com.citycloud.ccuap.tc.admin.entity.SysProjectFunction;
import com.citycloud.ccuap.tc.yq.constant.YqConstant;

@Mapper
public interface SecUserMapper {
    @SelectProvider(type = SecUserMapper.SqlProvider.class, method = "findRoleListByUserIdAndProjectId")
    @ResultMap("com.citycloud.ccuap.commons.sys.entity.SysRole.ResultMapExt")
    public List<SysRole> findRoleListByUserIdAndProjectId(Long userId, Long projectId);

    @SelectProvider(type = SecUserMapper.SqlProvider.class, method = "findProjectRoles")
    @ResultMap("com.citycloud.ccuap.commons.sys.entity.SysRole.ResultMapExt")
    public List<SysRole> findProjectRoles(String projectId, String userId);

    @SelectProvider(type = SecUserMapper.SqlProvider.class, method = "getUsersByUserName")
    @ResultMap("com.citycloud.ccuap.commons.sys.entity.SysUser.ResultMapExt")
    public List<SysUser> getUsersByUserName(String userName);

    @SelectProvider(type = SecUserMapper.SqlProvider.class, method = "getUsersByRoleId")
    @ResultMap("com.citycloud.ccuap.commons.sys.entity.SysUser.ResultMapExt")
    public List<SysUser> getUsersByRoleId(Long roleId);

    @SelectProvider(type = SecUserMapper.SqlProvider.class, method = "findByProjectId")
    @ResultMap("com.citycloud.ccuap.commons.sys.entity.SysRole.ResultMapExt")
    public List<SysRole> findByProjectId(Long projectId);

    @SelectProvider(type = SecUserMapper.SqlProvider.class, method = "findProjectFuncsByRoles")
    @ResultMap("com.citycloud.ccuap.tc.admin.entity.SysProjectFunction.ResultMapExt")
    public List<SysProjectFunction> findProjectFuncsByRoles(String projectId, String roleIds, String pid);

    @SelectProvider(type = SecUserMapper.SqlProvider.class, method = "getSysProjectsByConditions")
    public List<Map<String, Object>> getSysProjectsByConditions(Long managerId, Long cateId, String name);

    @SelectProvider(type = SecUserMapper.SqlProvider.class, method = "getProjectsByUserRoles")
    @ResultMap("com.citycloud.ccuap.commons.sys.entity.SysProject.ResultMapExt")
    public List<SysProject> getProjectsByUserRoles(Long userId);

    @SelectProvider(type = SecUserMapper.SqlProvider.class, method = "getAllProjects")
    @ResultMap("com.citycloud.ccuap.commons.sys.entity.SysProject.ResultMapExt")
    public List<SysProject> getAllProjects();

    @SelectProvider(type = SecUserMapper.SqlProvider.class, method = "getWebSitesByRoleIdAndUserId")
    List<Map<String, Object>> getWebSitesByRoleIdAndUserId(String userId, String roleCode);

    @SelectProvider(type = SecUserMapper.SqlProvider.class, method = "findUndeletedRoles")
    @ResultMap("com.citycloud.ccuap.commons.sys.entity.SysRole.ResultMapExt")
    public List<SysRole> findUndeletedRoles();

    @SelectProvider(type = SecUserMapper.SqlProvider.class, method = "findRolesByDataGroupId")
    @ResultMap("com.citycloud.ccuap.commons.sys.entity.SysRole.ResultMapExt")
    List<SysRole> findRolesByDataGroupId(String datagroupId);

    @SelectProvider(type = SecUserMapper.SqlProvider.class, method = "getRoleIdsByProjectIdAndUserId")
    @ResultType(value = Long.class)
    public List<Long> getRoleIdsByProjectIdAndUserId(Long projectId, Long usrId);

    @SelectProvider(type = SecUserMapper.SqlProvider.class, method = "findProjectFunsSetByUserIdandProjectId")
    @ResultType(value = String.class)
    public Set<String> findProjectFunsSetByUserIdandProjectId(Long projectId, Long userId);

    @DeleteProvider(type = SecUserMapper.SqlProvider.class, method = "deleteUnUseredByProjectId")
    void deleteUnUseredByProjectId(Long projectId);

    @SelectProvider(type = SecUserMapper.SqlProvider.class, method = "selectAllProjectByUserId")
    @ResultMap("com.citycloud.ccuap.commons.sys.entity.SysProject.ResultMapExt")
    List<SysProject> selectAllProjectByUserId(String userId);

    @SelectProvider(type = SecUserMapper.SqlProvider.class, method = "getTablePermissionsByUser")
    List<Map<String, String>> getTablePermissionsByUser(String cateId, String tableIds);

    @SelectProvider(type = SecUserMapper.SqlProvider.class, method = "getManageUserByProjectId")
    @ResultMap("com.citycloud.ccuap.commons.sys.entity.SysUser.ResultMapExt")
    List<SysUser> getManageUserByProjectId(String projectId);

    @Cacheable(cacheNames = YqConstant.CACHE_KEY_CCUAP_SYS_USERS, key = "#p0")
    @SelectProvider(type = SecUserMapper.SqlProvider.class, method = "getUsersByDeptId")
    List<Map<String, Object>> getUsersByDeptId(Long deptId);

    @CachePut(cacheNames = YqConstant.CACHE_KEY_CCUAP_SYS_USERS, key = "#p0")
    @SelectProvider(type = SecUserMapper.SqlProvider.class, method = "getUsersByDeptId")
    List<Map<String, Object>> getListUsersByDeptId(Long deptId);

    @SelectProvider(type = SecUserMapper.SqlProvider.class, method = "getAllCustomCode")
    @ResultMap("com.citycloud.ccuap.commons.sys.entity.SysGroup.ResultMapExt")
    List<SysGroup> getAllCustomCode(String oriCustom);

    @SelectProvider(type = SecUserMapper.SqlProvider.class, method = "findByGroupNo")
    @ResultMap("com.citycloud.ccuap.commons.sys.entity.SysGroup.ResultMapExt")
    List<SysGroup> findByGroupNo(String groupNo);

    /**
     * 获取部门导入的人员
     *
     * @param deptId
     * @param projectId
     * @return
     */
    @SelectProvider(type = SecUserMapper.SqlProvider.class, method = "getDatacListUsersByDeptId")
    List<Map<String, Object>> getDatacListUsersByDeptId(Long deptId, Long projectId);

    /**
     * 获取部门自建人员
     *
     * @param deptId
     * @param projectId
     * @return
     */
    @SelectProvider(type = SecUserMapper.SqlProvider.class, method = "getDatacListUsersByDeptIdAndProjectId")
    List<Map<String, Object>> getDatacListUsersByDeptIdAndProjectId(Long deptId, Long projectId);

    @SelectProvider(type = SecUserMapper.SqlProvider.class, method = "getTablePermissionsByUserNew")
    List<Map<String, String>> getTablePermissionsByUserNew(String userId, String projectId);

    class SqlProvider {

        public String getDatacListUsersByDeptIdAndProjectId(Long deptId, Long projectId) {
            StringBuffer sql = new StringBuffer("select sys_user.user_id userId, user_guid userGuid,user_no userNo,user_name userName, card_no cardNo,sex sex,sys_user.dept_id deptId,telephone telephone,address address,email email,birthday birthday, id_card idCard, age age,theme theme,auth_start_date authStartDate,auth_end_date endAuthEndDate,data_status   dataStatus,json json,creator creator , created_time  createdTime, updated_time updator,remark remark,office_tel officeTel,user_grade userGrade,type type,icon_file_id iconFileId,icon_file_path iconFilePath,enabled enabled,   type userType from sys_user WHERE sys_user.project_id = " + projectId + " and sys_user.dept_id = " + deptId + "");
            return sql.toString();
        }

        public String getDatacListUsersByDeptId(Long deptId, Long projectId) {
            StringBuffer sql = new StringBuffer("select sys_user.user_id userId, user_guid userGuid,user_no userNo,user_name userName, card_no cardNo,sex sex,sys_user.dept_id deptId,telephone telephone,address address,email email,birthday birthday, id_card idCard, age age,theme theme,auth_start_date authStartDate,auth_end_date endAuthEndDate,data_status   dataStatus,json json,creator creator , created_time  createdTime, updated_time updator,remark remark,office_tel officeTel,user_grade userGrade,type type,icon_file_id iconFileId,icon_file_path iconFilePath,enabled enabled,   type userType from sys_user LEFT JOIN sys_user_dept_rel ON sys_user_dept_rel.user_id = sys_user.user_id WHERE sys_user_dept_rel.project_id = " + projectId + " ");
            sql.append(SqlRestrictions.eq("sys_user_dept_rel.dept_id", deptId));
            return sql.toString();
        }

        public String getUsersByDeptId(Long deptId) {
            StringBuffer sql = new StringBuffer("select user_id userId, user_guid userGuid,user_no userNo,user_name userName, card_no cardNo,sex sex,dept_id deptId,telephone telephone,address address,email email,birthday birthday, id_card idCard, age age,theme theme,auth_start_date authStartDate,auth_end_date endAuthEndDate,data_status   dataStatus,json json,creator creator , created_time  createdTime, updated_time updator,remark remark,office_tel officeTel,user_grade userGrade,type type,icon_file_id iconFileId,icon_file_path iconFilePath,enabled enabled,  type userType from sys_user where 1=1 ");
            sql.append(SqlRestrictions.eq("dept_id", deptId));
            return sql.toString();
        }

        public String findProjectFunsSetByUserIdandProjectId(Long projectId, Long userId) {
            StringBuffer sql = new StringBuffer("select t2.fun_id from sys_project_acl t1 inner join sys_project_function t2 on t1.prj_fun_id = t2.prj_fun_id ");
            sql.append(SqlRestrictions.eq("t2.project_id", projectId));
            sql.append(" inner join sys_act t3 on t3.role_id = t1.role_id ");
            sql.append(SqlRestrictions.eq("t3.user_id", userId));
            return sql.toString();
        }

        public String deleteUnUseredByProjectId(Long projectId) {
            String sql = "delete t1.* from sys_project_dg_role_rel t1 inner join sys_role t2 on t1.role_id = t2.role_id  and t2.data_status = 1 where t1.datagroup_id not in (select t3.datagroup_id from sys_project_dg_rel t3 where t3.project_id = t2.project_id ) ";
            sql = sql + SqlRestrictions.eq("t2.project_id", projectId);
            return sql;
        }

        public String getRoleIdsByProjectIdAndUserId(Long projectId, Long usrId) {
            StringBuffer sql = new StringBuffer("select distinct t2.role_id  from sys_role t1 inner join sys_act t2 on t1.role_id = t2.role_id and t1.data_status = 1");
            sql.append(SqlRestrictions.eq("t1.project_id", projectId));
            sql.append(SqlRestrictions.eq("t2.user_id", usrId));
            return sql.toString();
        }

        public String findUndeletedRoles(Long projectId) {
            return "select * from sys_role where data_status = 1 and project_id is not null";
        }

        public String findRolesByDataGroupId(String projectId) {
            String sql = "select t1.* from sys_role t1 inner join sys_project_dg_role_rel t2 on t1.role_id = t2.role_id  and t1.data_status = 1 and t1.project_id is not null ";
            sql = sql + SqlRestrictions.eq("t2.datagroup_id", projectId);
            return sql;
        }

        public String getProjectsByUserRoles(Long userId) {
            StringBuilder sb = new StringBuilder("select t.* from sys_project t where t.data_status = 1 and project_id in ( ");
            sb.append("select r.project_id from sys_role r inner join sys_act a on r.role_id = a.role_id and a.user_id = ");
            sb.append(userId).append(" where r.data_status = 1) ");
            return sb.toString();
        }

        public String getAllProjects() {
            StringBuilder sb = new StringBuilder("select t.* from sys_project t where t.data_status = 1");
            return sb.toString();
        }

        public String getSysProjectsByConditions(Long managerId, Long cateId, String name) {
            StringBuilder sb = new StringBuilder("select tenant.project_id as tenantId, tenant.project_name as tenanttName, cate.cat_name as categoryName, ");
            sb.append("tenant.url as url, tenant.cat_id as categoryId, tenant.icon_path as iconPath, tenant.background_path as backgroundPath, ");
            sb.append("tenant.remark as remark ");
            sb.append("from sys_project tenant left join sys_project_category cate on cate.cat_id = tenant.cat_id where tenant.data_status = 1 ");
            if (null != managerId) {
                sb.append(" and tenant.project_id in (select tm.project_id from sys_project_manager tm where tm.manager_id = ").append(managerId).append(") ");
            }
            if (null != cateId) {
                sb.append("and tenant.cat_id in (select cate.cat_id from sys_project_category cate where cate.cat_id = ").append(cateId)
                        .append(" or cate.pid = ").append(cateId).append(") ");
            }
            if (StringUtils.isNotBlank(name)) {
                sb.append("and tenant.project_name like '%").append(name).append("%'");
            }
            return sb.toString();
        }

        public String findProjectFuncsByRoles(String projectId, String roleIds, String pid) {
            StringBuilder sql = new StringBuilder("select distinct fun.* from sys_project_function fun inner join sys_project_acl acl on fun.prj_fun_id = acl.prj_fun_id ");
            if (StringUtils.isNotBlank(projectId)) {
                sql.append("and fun.project_id = ").append(projectId);
            }
            sql.append(" where 1=1 ");
            if (StringUtils.isBlank(pid)) {
                sql.append("and fun.pid is null ");
            } else {
                sql.append("and fun.pid = '").append(pid).append("' ");
            }
            sql.append("and acl.role_id in (").append(roleIds).append(") order by fun.order_no ");
            return sql.toString();
        }

        public String findByProjectId(Long projectId) {
            String sql = "select * from sys_role where 1=1 ";
            sql += SqlRestrictions.eq("project_id", projectId);
            return sql;
        }

        public String getUsersByRoleId(Long roleId) {
            StringBuilder sb = new StringBuilder("select u.* from sys_user u inner join sys_act a on a.user_id = u.user_id and ");
            sb.append("a.role_id = ").append(roleId).append(" where u.data_status = 1 ");
            return sb.toString();
        }

        public String getUsersByUserName(String userName) {
            StringBuilder sb = new StringBuilder("select * from sys_user where data_status = 1 ");
            if (StringUtils.isNotBlank(userName)) {
                sb.append("and user_name like '%").append(userName).append("%' ");
            }
            return sb.toString();
        }

        public String findRoleListByUserIdAndProjectId(Long userId, Long projectId) {
            String sql = "SELECT role.* FROM sys_role role,sys_act act where role.role_id=act.role_id";
            sql += SqlRestrictions.eq("act.user_id", userId);
            if (projectId == null) {
                sql += " and role.project_id is null";
            } else {
                sql += SqlRestrictions.eq("role.project_id", projectId);
            }
            return sql;
        }

        public String findProjectRoles(String projectId, String userId) {
            StringBuilder sql = new StringBuilder("select role.* from SYS_ROLE role inner join SYS_ACT act on role.role_id = act.role_id ");
            sql.append("where role.data_status = 1 ");
            if (StringUtils.isNotBlank(projectId)) {
                sql.append("and role.project_id = ").append(projectId).append(" ");
            }
            if (StringUtils.isNotBlank(userId)) {
                sql.append("and act.user_id = ").append(userId).append(" ");
            }
            return sql.toString();
        }

        public String getWebSitesByRoleIdAndUserId(String userId, String roleCode) {
            StringBuilder sql = new StringBuilder("SELECT project.project_id AS projectId, project.project_name AS projectName FROM sys_role role INNER JOIN sys_project project ON role.project_id = project.project_id LEFT JOIN sys_act act ON act.role_id = role.role_id");
            sql.append(" WHERE 1 = 1 ");
            if (StringUtils.isNotBlank(roleCode)) {
                sql.append("AND role.role_code = '").append(roleCode).append("' ");
            }
            if (StringUtils.isNotBlank(userId)) {
                sql.append("AND act.user_id = '").append(userId).append("' ");
            }
            return sql.toString();
        }

        public String selectAllProjectByUserId(String userId) {
            String sql = "SELECT DISTINCT sp.* FROM sys_project sp INNER JOIN sys_user_dept_rel sudr ON sp.project_id = sudr.project_id" +
                    " INNER JOIN sys_user su ON su.user_id = sudr.user_id WHERE sp.data_status = 1 ";
            if (StringUtils.isNotBlank(userId)) {
                sql += SqlRestrictions.eq("su.user_id", userId);
            }
            return sql;
        }

        public String getTablePermissionsByUser(String cateId, String tableIds) {
            if (StringUtils.isNotBlank(cateId)) {
                String sql = "SELECT tv.TABLE_CNAME tableCname,cv.OBJ_ID cateId,cv.CATE_NAME cateName " +
                        "FROM ap_t_meta_table_cate_view cv LEFT JOIN ap_t_meta_table_view tv ON tv.CATE_ID = cv.OBJ_ID " +
                        "WHERE 1=1 ";
                sql += SqlRestrictions.in("tv.OBJ_ID", tableIds);
                sql += SqlRestrictions.eq("cv.PID", cateId);
                return sql;
            } else {
                String sql = "SELECT tcv.OBJ_ID cateId,tcv.CATE_NAME cateName FROM ap_t_meta_table_cate_view tcv " +
                        "WHERE tcv.OBJ_ID IN (SELECT cv.PID pId " +
                        "FROM ap_t_meta_table_cate_view cv LEFT JOIN ap_t_meta_table_view tv ON tv.CATE_ID = cv.OBJ_ID " +
                        "WHERE 1=1 ";
                sql += SqlRestrictions.in("tv.OBJ_ID", tableIds);
                return sql + ")";

            }
        }

        public String getManageUserByProjectId(String projectId) {
            if (StringUtils.isNotBlank(projectId)) {
                String sql = "SELECT u.* FROM sys_project_manager m LEFT JOIN sys_user u ON m.manager_id = u.user_id WHERE 1=1 ";
                sql += SqlRestrictions.in("m.project_id", projectId);
                return sql;
            } else {
                String sql = "SELECT DISTINCT u.* FROM sys_user u INNER JOIN sys_act a ON u.user_id = a.user_id " +
                        "INNER JOIN sys_role r ON a.role_id = r.role_id WHERE r.project_id IS NULL";
                return sql;
            }
        }

        public String getAllCustomCode(String oriCustom) {
            if (StringUtils.isNotBlank(oriCustom)) {
                String sql = "SELECT t.* FROM sys_group AS t where t.group_no like '%00' order by t.group_no";
                return sql;
            } else {
                String sql = "SELECT t.* FROM sys_group AS t order by t.group_no";
                return sql;

            }
        }

        public String findByGroupNo(String groupNo) {
            String sql = "SELECT t.* FROM sys_group AS t where 1=1 ";
            sql += SqlRestrictions.eq("t.group_no", groupNo);
            sql = sql + SqlRestrictions.order("t.group_no", Sort.Direction.ASC);
            return sql;
        }

        public String getTablePermissionsByUserNew(String userId, String projectId) {
            StringBuffer sql = new StringBuffer("SELECT" +
                    "( SELECT tcv.CATE_NAME FROM ap_t_meta_table_cate_view AS tcv WHERE tcv.OBJ_ID = sdtc.first_cat_id ) AS cateId," +
                    "( SELECT tcv.CATE_NAME FROM ap_t_meta_table_cate_view AS tcv WHERE tcv.OBJ_ID = sdtc.second_cat_id ) AS cateName," +
                    "( SELECT tv.TABLE_CNAME FROM ap_t_meta_table_view AS tv WHERE tv.OBJ_ID = sdtc.table_id ) AS tableCname " +
                    " FROM " +
                    " sys_dimension_table_config AS sdtc " +
                    " WHERE " +
                    " sdtc.datagroup_id IN ( " +
                    " SELECT " +
                    " spdrl.datagroup_id " +
                    " FROM " +
                    " sys_project_dg_role_rel AS spdrl " +
                    " WHERE " +
                    " spdrl.role_id IN ( " +
                    " SELECT role_id FROM sys_role sr " +
                    " WHERE 1=1 and sr.role_id IN ( SELECT sa.role_id FROM sys_act sa WHERE user_id = ");
            sql.append(userId).append(")");
            if(null != projectId){
                sql.append("AND sr.project_id = ").append(projectId).append(") ");
            }
            sql.append(")");
            return sql.toString();
        }
    }


}
